#!/bin/bash

# 可以输入日期;如果未输入日期取昨天的时间
if [ -n "$1" ]; then
  do_date=$1
else
  do_date=$(date -d "-1 day" +%F)
fi

sql="
with tmp as
         (select device_id,
                 dt,
                 row_number() over (partition by device_id order by dt) day
          from dws.dws_member_start_day
          where dt >= date_add('$do_date', -6)
            and dt <= '$do_date')
select device_id, dt, day
from tmp
where day > 3
group by device_id, dt, day
"

hive -e "$sql"
